package authoritah.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;

public class Spend {
	private int id;
	private Timestamp date;
	private int pointsSpent;
	private Event event;
	private Item item;
	private Member member;
	
	/////////////////////////
	// Getters and Setters //
	/////////////////////////
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public Timestamp getDate() {
		return date;
	}
	public void setDate(Timestamp date) {
		this.date = date;
	}
	public int getPointsSpent() {
		return pointsSpent;
	}
	public void setPointsSpent(int pointsSpent) {
		this.pointsSpent = pointsSpent;
	}
	public Event getEvent() {
		return event;
	}
	public void setEvent(Event event) {
		this.event = event;
	}
	public Member getMember() {
		return member;
	}
	public void setMember(Member member) {
		this.member = member;
	}
	public Item getItem() {
		return item;
	}
	public void setItem(Item item) {
		this.item = item;
	}
	
	//////////////////////
	// Database methods //
	//////////////////////
	
	/**
	 * Retrieves a member from the database file. If no member with supplied id exists, returns null.
	 * @param id
	 * @return
	 */
	public static Spend getSpend(int id){
		Spend spend = new Spend();
		try {
            ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM spend WHERE id=?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            
            // Only interested in first result as id is primary key should never have more than result anyway.
        	rs.next();            	
            spend.setId(rs.getInt("id"));
            spend.setDate(new Timestamp(rs.getLong("date")));
            spend.setEvent(Event.getEvent(rs.getInt("event_id")));
            spend.setItem(Item.getItem(rs.getInt("item_id")));
            spend.setMember(Member.getMember(rs.getInt("member_id")));
            spend.setPointsSpent(rs.getInt("points_spent"));
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        return spend;
	}
	
	/**
	 * Retrieves a member from the database file. If no member with supplied name exists, returns null.
	 * @param name
	 * @return
	 */
	public static Spend getSpend(String name){
		Spend spend = new Spend();
		try {
            ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM spend WHERE name=?");
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            
            // Only interested in first result as id is primary key should never have more than result anyway.
        	rs.next();            	
            spend.setId(rs.getInt("id"));
            spend.setDate(new Timestamp(rs.getLong("date")));
            spend.setEvent(Event.getEvent(rs.getInt("event_id")));
            spend.setItem(Item.getItem(rs.getInt("item_id")));
            spend.setMember(Member.getMember(rs.getInt("member_id")));
            spend.setPointsSpent(rs.getInt("points_spent"));
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return spend;
	}
	
	/**
	 * Stores a member into the database.
	 * @param spend
	 */
	public static void createSpend(Spend spend){
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("INSERT INTO spend (date, points_spent, event_id, item_id, member_id) VALUES (?, ?, ?, ?, ?)");
            ps.setLong(1, spend.getDate().getTime());
            ps.setInt(2, spend.getPointsSpent());
            ps.setInt(3, spend.getEvent().getId());
            ps.setInt(4, spend.getItem().getId());
            ps.setInt(5, spend.getMember().getId());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
	}

	public static void updateSpend(Spend spend) {
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("UPDATE spend SET date=?, points_spent=?, event_id=?, item_id=?, member_id=?  WHERE id=?");
            ps.setLong(1, spend.getDate().getTime());
            ps.setInt(2, spend.getPointsSpent());
            ps.setInt(3, spend.getEvent().getId());
            ps.setInt(4, spend.getItem().getId());
            ps.setInt(5, spend.getMember().getId());
            ps.setInt(6, spend.getId());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }		
	}
	public static void removeSpend(Spend spend) {
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("DELETE FROM spend WHERE id = ?");
            ps.setInt(1, spend.getId());
            ps.executeUpdate();
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }	
	}
	
	/**
	 * Calculates all points spent by a particular member.
	 */
	public static int getPointsSpent(Member member) {
		int pointsSpent = 0;
		try {
			ParityDB db = ParityDB.getInstance();
            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT points_spent FROM spend WHERE member_id=?");
            ps.setInt(1, member.getId());
            
            ResultSet rs = ps.executeQuery();
            
            // Iterate the result set, printing each column
            while (rs.next()) {            	
                pointsSpent += rs.getInt("points_spent");
            }            
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        return pointsSpent;
	}
	public static ArrayList<Spend> getAllSpends() {
		ArrayList<Spend> spends = new ArrayList<Spend>();
		try {
			ParityDB db = ParityDB.getInstance();            
            Connection conn = db.getConn();
            
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM spend");
            ResultSet rs = ps.executeQuery();
            
            // Iterate the result set, adding to the collection.
            while (rs.next()) {
            	Spend spend = new Spend();
            	spend.setDate(new Timestamp(rs.getLong("date")));
                spend.setEvent(Event.getEvent(rs.getInt("event_id")));
                spend.setItem(Item.getItem(rs.getInt("item_id")));
                spend.setMember(Member.getMember(rs.getInt("member_id")));
                spend.setPointsSpent(rs.getInt("points_spent"));
                spends.add(spend);
            }
            
            // Close the connection
            conn.close();
            
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return spends;
	}
}
